import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource
import org.codehaus.groovy.grails.commons.GrailsApplication
import grails.util.GrailsUtil

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author yoann
 */
class DBConnection {

        static Connection conn = null ;
        private static Statement stmt = null;
        private static ResultSet rs = null;
        private static String query = null ;


    /** Connection to the database   **/
	public static void connect() throws Exception {
            if(!isConnectionStarted()){
		try {

                // datas for development environment
                String url = "jdbc:mysql://2009-2.pumi.ida.liu.se:3306/megasoft_dev_DB";
                String utilisateur = "megauser";
                String mdp = "qwerty";
//                 String url = "jdbc:mysql://localhost/abdri234";
//                String utilisateur = "root";
//                String mdp = "root";

                if ( GrailsUtil.getEnvironment().equals(GrailsApplication.ENV_PRODUCTION)){
                    url = "jdbc:mysql://2009-2.pumi.ida.liu.se:3306/megasoft_prod_DB"
                }
                if ( GrailsUtil.getEnvironment().equals(GrailsApplication.ENV_TEST)){
                    url = "jdbc:mysql://2009-2.pumi.ida.liu.se:3306/megasoft_test_DB"
                }

                        println("Try to connect")
			MysqlDataSource ods = new MysqlDataSource();
			ods.setUser(utilisateur);
			ods.setPassword(mdp);
			ods.setURL(url);
			conn = ods.getConnection();

                        println("connected")

		} catch (SQLException e) {
			throw new Exception ("connexion impossible \n" + e.getMessage() + " code oracle " + e.getErrorCode());
		}
            }
	}
        public static String returnKey(String error){
            String[] str=error.split(" ")
            Integer i=0
            while(!str[i].equals("entry")){
                    i++
            }
            i++
            String[] strWith=str[i].split("'")
                return strWith[1]
        }

    public static Long returnKeyEmpl(String error){
            String[] str=error.split(" ")
            Integer i=0
            while(!str[i].equals("entry")){
                    i++
            }
            i++
            String[] strWith=str[i].split("'")

            Long res = Long.parseLong(strWith[1])
            return res
        }

    /** closes all connections with the database **/
    public static void closeAll(){
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				;
			}
			rs = null;
		}
		if (stmt != null) {
			try {
				stmt.close();
			}
			catch (SQLException e) {
				;
			}
			stmt = null;
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				;
			}
			conn = null;
		}
	}

    /** tests whether a transaction is opened or not **/
    public static boolean isConnectionStarted(){
        return (conn!=null);
    }


    /** start a new transaction **/
    public static void startTransaction(){
        if(isConnectionStarted()){
            stmt = conn.createStatement();
            query = "start transaction";
            stmt.executeQuery(query);
            println("transaction started!")
        }
    }

    /** rollback the running transaction */
    public static void rollbackTransaction(String currentMode){
        if(isConnectionStarted()){
            stmt = conn.createStatement();
            query = "rollback";
            stmt.executeQuery(query);
            println("transaction RolledBack!")
            //session.changes=false
            closeAll()
            if(currentMode==null || currentMode.equalsIgnoreCase("sandbox")){
                DBConnection.connect()
                DBConnection.startTransaction()
            }
        }
    }

    /** commits the transaction, the current decides whether we should restart a new transaction (sandbox mode) after the commit **/
    public static void commitTransaction(String currentMode){
        if(isConnectionStarted()){
            stmt = conn.createStatement();
            query = "commit";
            stmt.executeQuery(query);
            println(" transaction commited!")
            closeAll()
            if(currentMode==null || currentMode.equalsIgnoreCase("sandbox")){
                DBConnection.connect()
                DBConnection.startTransaction()
            }
        }
    }

    /** changes the engine of the given table **/
    public static void alterTableEngine(String table){
        stmt = conn.createStatement();
        query = "alter table "+table+" engine=InnoDB"
        stmt.executeUpdate(query);
    }


    
    /**************************************************************************/
    /*******         Access to the database concerning projects         *******/
    /**************************************************************************/

    /** get the project specified by its Id and create it as an object **/
    public static Project getProject(String project){
        if(isConnectionStarted()){
            stmt = conn.createStatement();
            query = "select id,activity_id,area_id,description,end_date,mode,name,start_date from project where id="+project;
            rs = stmt.executeQuery(query);
            rs.next()
            Area a = Area.get(rs.getString(3))
            Project p = new Project(area:a,activityId:rs.getString(2),description:rs.getString(4),name:rs.getString(7),mode:rs.getString(6),startDate:new Date(rs.getString(8).substring(0,10).replace('-','/')),endDate:new Date(rs.getString(5).substring(0,10).replace('-','/')))
            p.id=Long.parseLong(rs.getString(1))
            for(Whoa w :getWhoaList(p)){
                p.addToAmountOfHour(w);
            }
            return p
        }
        else{
            return Project.get(project)
        }
    }

    /** get the project specified by its Id and create it as an object **/
    public static Project getProject(ResultSet r){
        //query = "select id,activity_id,area_id,description,end_date,mode,name,start_date from project where id="+project;
        Area a = Area.get(r.getString(3))
        Project p = new Project(area:a,activityId:r.getString(2),description:r.getString(4),name:r.getString(7),mode:r.getString(6),startDate:new Date(r.getString(8).substring(0,10).replace('-','/')),endDate:new Date(r.getString(5).substring(0,10).replace('-','/')))
        p.id=Long.parseLong(r.getString(1))
        for(Whoa w :getWhoaList(p)){
            p.addToAmountOfHour(w);
        }
        return p
    }

    /** update the given project **/
    public static Integer updateProject(Project p){
 try{
        if(isConnectionStarted()){
            stmt = conn.createStatement();
            query = "update project set name='"+p.name+"', "
            query += "activity_id='"+p.activityId+"', "
            query += "area_id='"+p.area.id+"', "
            query += "description='"+p.description+"', "
            query += "end_date='"+p.endDate.format('yy/MM/dd')+"', "
            query += "name='"+p.name+"', "
            query += "start_date='"+p.startDate.format('yy/MM/dd')+"'"
            query += " where id="+p.id;
            stmt.executeUpdate(query);

            //println("executed 1")
            stmt = conn.createStatement();
            query = "update project set activity_id='"+p.activityId+"' where id="+p.id;
            stmt.executeUpdate(query);
            //println("executed 2")
            DBConnection.updateAmountOfHour(p)
            return 1
        }
        }
         catch(SQLException ex){
             if(ex.getErrorCode()==1062 ){
              if(returnKey(ex.getMessage()).equals(p.activityId)){
                  return -2
              }
               if(returnKey(ex.getMessage()).equals(p.name)){
                  return -3
              }
             }
             return -1
        }
        return 1
    }

    /** gets the list of projects **/
    public static List<Project> getProjectList(){
        ArrayList<Project> l = new ArrayList<Project>()
        if(isConnectionStarted()){
            ResultSet rs2
            stmt = conn.createStatement();
            // TODO filter with the current container
            query = "select id,activity_id,area_id,description,end_date,mode,name,start_date from project where mode=1";
            rs2 = stmt.executeQuery(query);
            while (rs2.next()){
//               println("get project:"+rs2.getString(1))
               l.add(DBConnection.getProject(rs2))
            }
        }
        return l
    }

    /** get the count of project **/
    public static Integer getProjectCount(){
        ArrayList<Project> l = new ArrayList<Project>()
        if(isConnectionStarted()){
            stmt = conn.createStatement();
            // TODO filter with the current container
            query = "select count(id) from project where mode=1";
            rs = stmt.executeQuery(query);
            rs.next()
            return Integer.parseInt(rs.getString(1))
        }
        else{
            return 0
        }
    }

    /** get a list of projects for a specific area **/
    public static List<Project> findProjectByArea(Area a){
        ArrayList<Project> l = new ArrayList<Project>()
        if(isConnectionStarted()){
            ResultSet rs2
            stmt = conn.createStatement();
            // TODO filter with the current container
            query = "select id from project where mode=1 and area_id="+a.id;
            rs2 = stmt.executeQuery(query);
            while (rs2.next()){
               l.add(DBConnection.getProject(rs2.getString(1)))
            }
        }
        return l
    }

    /** creates the given project in the database **/
    public static Integer saveProject(Project p){
        try{
            stmt = conn.createStatement();
            //query = "insert into project values(0,0,'"+p.activityId+"',"+p.area.id+",'"+p.description+"','"+p.endDate.format('yy/MM/dd')+"',"+p.mode+",'"+p.name+"','"+p.startDate.format('yy/MM/dd')+"',null,null)";
            query = "insert into project values(0,0,'"+p.activityId+"',"+p.area.id+",'"+p.description+"','"+p.endDate.format('yy/MM/dd')+"',"+p.mode+",'"+p.name+"','"+p.startDate.format('yy/MM/dd')+"')";
            stmt.execute(query);
            stmt = conn.createStatement();
            query = "select max(id) from project where mode=1";
            rs = stmt.executeQuery(query);
            rs.next()
//            println("project inserted")
            return Integer.parseInt(rs.getString(1))
        }
        catch(SQLException ex){
             if(ex.getErrorCode()==1062 ){
              if(returnKey(ex.getMessage()).equals(p.activityId)){
                  return -2
              }
               if(returnKey(ex.getMessage()).equals(p.name)){
                  return -3
              }
             }
             return -1
        }

        catch(Exception e){

            //here fix for mysql errors
            println("exc Save project:"+e)
            return -1
        }
    }

    /** delete the project with the given Id from the database **/
    public static boolean deleteProject(Long pId){
        try{
            stmt = conn.createStatement();
            query = "delete from project where id="+pId;

            stmt.execute(query);
//            println("project deleted")
            return true
        }
        catch(Exception e){
            println("exc delete project:"+e)
            return false
        }
    }


    /**************************************************************************/
    /*******        Access to the database concerning employees         *******/
    /**************************************************************************/

    /**
    * This function returns the list of all employees
    **/
    public static List<Employee> getEmployeeList(){
        if(isConnectionStarted()){
            List<Employee> res = new ArrayList<Employee>()
            if(isConnectionStarted()){
                ResultSet rs2
                stmt = conn.createStatement();
                // TODO filter with the current container
                query = "select id,city,dob,email,employee_id,first_name,hired_by_id,last_name,mode,percentage_work,phone_number,salary,street,title,zip_code from employee where mode=1";
                rs2 = stmt.executeQuery(query);
                while (rs2.next()){
                   res.add(DBConnection.getEmployee(rs2))
                }
            }
            return res
        }
        else{
            return Employee.list()
        }
    }

    /**
    * This function returns the list of all employees
    **/
    public static List<Employee> getEmployeeListByArea(Area area){
        if(isConnectionStarted()){
            List<Employee> res = new ArrayList<Employee>()
            if(isConnectionStarted()){
                ResultSet rs2
                stmt = conn.createStatement();
                // TODO filter with the current container
                query = "select id,city,dob,email,employee_id,first_name,hired_by_id,last_name,mode,percentage_work,phone_number,salary,street,title,zip_code from employee where mode=1 and hired_by_id="+area.id;
                rs2 = stmt.executeQuery(query);
                while (rs2.next()){
                   res.add(DBConnection.getEmployee(rs2))
                }
            }
            return res
        }
        else{
            return Employee.findAllByHiredBy(area)
        }
    }

    /** get the employee specified by its Id and create it as an object **/
    public static Employee getEmployee(String employee){
        if(isConnectionStarted()){
            stmt = conn.createStatement();
            query = "select id,city,dob,email,employee_id,first_name,hired_by_id,last_name,mode,percentage_work,phone_number,salary,street,title,zip_code from employee where id="+employee;
            //println("quer:"+query)
            rs = stmt.executeQuery(query);
            rs.next()
            Area a = Area.get(rs.getString(7))
            Employee e = new Employee(percentageWork:rs.getFloat(10),hiredBy:a,employeeId:rs.getString(5),firstName:rs.getString(6),lastName:rs.getString(8),dob:new Date(rs.getString(3).substring(0,10).replace('-','/')),title:rs.getString(14),salary:rs.getString(12),email:rs.getString(4),street:rs.getString(13),phoneNumber:rs.getString(11),city:rs.getString(2),zipCode:rs.getString(15),mode:rs.getString(9))
            e.id=Integer.parseInt(rs.getString(1))
            return e
        }
        else{
            return Employee.get(employee)
        }
    }

    /** get the employee specified by its Id and create it as an object **/
    public static Employee getEmployee(ResultSet r){
            Area a = Area.get(r.getString(7))
            Employee e = new Employee(percentageWork:r.getFloat(10),hiredBy:a,employeeId:r.getString(5),firstName:r.getString(6),lastName:r.getString(8),dob:new Date(r.getString(3).substring(0,10).replace('-','/')),title:r.getString(14),salary:r.getString(12),email:r.getString(4),street:r.getString(13),phoneNumber:r.getString(11),city:r.getString(2),zipCode:r.getString(15),mode:r.getString(9))
            e.id=Integer.parseInt(r.getString(1))
            return e
    }

    /** creates the given employee in the database **/
    public static Integer saveEmployee(Employee e){
        try{
            stmt = conn.createStatement();
            def area = null
            if(e.hiredBy){
                area=e.hiredBy.id
            }

            query = "insert into employee(version,city,dob,email,employee_id,first_name,hired_by_id,last_name,mode,percentage_work,phone_number,salary,street,title,zip_code,is_applicant) values(0,'"+e.city+"','"+e.dob.format('yy/MM/dd')+"','"+e.email+"','"+e.employeeId+"','"+e.firstName+"',"+area+",'"+e.lastName+"',"+e.mode+","+e.percentageWork+",'"+e.phoneNumber+"',"+e.salary+",'"+e.street+"','"+e.title+"',"+e.zipCode+",0)";

           // query = "insert into employee values(0,0,'"+e.city+"','"+e.dob.format('yy/MM/dd')+"','"+e.email+"','"+e.employeeId+"','"+e.firstName+"',"+area+",0,'"+e.lastName+"',"+e.mode+","+e.percentageWork+",'"+e.phoneNumber+"',"+e.salary+",'"+e.street+"','"+e.title+"',"+e.zipCode+")";

            //query = "insert into employee values(0,0,'"+e.city+"','"+e.dob.format('yy/MM/dd')+"','"+e.email+"',"+e.employeeId+",'"+e.firstName+"',"+area+",'"+e.lastName+"',"+e.mode+","+e.percentageWork+",'"+e.phoneNumber+"',"+e.salary+",'"+e.street+"','"+e.title+"',"+e.zipCode+",null,null,null,null)";
            //println("query: " + query)
            stmt.execute(query);
            
            stmt = conn.createStatement();
            query = "select max(id) from employee where mode=1";
            println("la")
            rs = stmt.executeQuery(query);
            rs.next()
            println("employee inserted")
            return Integer.parseInt(rs.getString(1))
        }
        catch(SQLException ex){
            println("res:"+ex)
            println("code:"+ex.getErrorCode())
             if(ex.getErrorCode().toString().equals("1062") ){
              if(returnKeyEmpl(ex.getMessage())==e.employeeId){
                  return -2
              }
             }
             println("exc Save employee 1:"+ex)
             return -1
        }
        catch(Exception ex){

            //mysql catch  different
            println("exc Save employee:"+ex)
            return -1
        }
    }

    /** update the given employee **/
    public static Integer updateEmployee(Employee emp){
        try{
        if(isConnectionStarted()){
            stmt = conn.createStatement();
            query = "update employee set city='"+emp.city+"', "
            query += "dob='"+emp.dob.format('yy/MM/dd')+"', "
            query += "email='"+emp.email+"', "
            query += "employee_id="+emp.employeeId+", "
            query += "first_name='"+emp.firstName+"', "
            def area = null
            if(emp.hiredBy){
                area=emp.hiredBy.id
            }
            query += "hired_by_id="+area+", "
            query += "last_name='"+emp.lastName+"', "
            query += "mode="+emp.mode+", "
            query += "percentage_work="+emp.percentageWork+", "
            query += "phone_number='"+emp.phoneNumber+"', "
            query += "salary="+emp.salary+", "
            query += "street='"+emp.street+"', "
            query += "title='"+emp.title+"', "
            query += "zip_code="+emp.zipCode
            query += " where id="+emp.id;

            stmt.executeUpdate(query);

                //mitt
            return 1 //wrong??
//            println("employee updated!")
        }
        }
         catch(SQLException ex){
              if(ex.getErrorCode()==1062 ){
            println(returnKey(ex.getMessage()))
              if(returnKey(ex.getMessage()).equals("employee_id")){
                return -2
              }
            //if(e= ){}
          return -1
        }
    }
}

    /** delete the employee with the given Id from the database **/
    public static boolean deleteEmployee(Long eId){
       if(DBConnection.isConnectionStarted()){
            try{
                def emplInstance = DBConnection.getEmployee(""+eId)
                for(Whoa w in getWorksIn(emplInstance)){
                    deleteWhoa(w)
                }
                
                stmt = conn.createStatement();
                query = "delete from employee where id="+eId;

                stmt.execute(query);
//                println("employee deleted")
                return true
            }
            catch(Exception e){
                println("exc delEmpl:"+e)
                return false
            }
       }
       else{
           def employeeInstance = Employee.get(eId)
           for(Whoa w in employeeInstance.worksIn){
               employeeInstance.removeFromWorksIn(w)
               w.delete()
            }
            employeeInstance.delete(flush:true)
       }
    }

    public static List getWorksIn(Employee emp){
//        println("want worksIn:"+emp)
        List<Whoa> res = new ArrayList<Whoa>()
        if(isConnectionStarted()){
            ResultSet rs2
            stmt = conn.createStatement();
            // TODO filter with the current container
            query = "select id,hours,works_in_project_id,year from whoa where works_in_project_id is not null and member_id="+emp.id;
            rs2 = stmt.executeQuery(query);
            while (rs2.next()){
//                println("found here")
               Whoa w = DBConnection.getWhoa(rs2)
               w.member=emp
               res.add(w)
            }
        }
        return res
    }

    /**************************************************************************/
    /*******          Access to the database concerning Whoa            *******/
    /**************************************************************************/
    
    /**
    * This function returns the list of Whoa for the project with the given Id
    **/
    public static List<Whoa> getWhoaList(Activity a){
//        println("get List for project: "+p)
        if("class Course".equalsIgnoreCase(a.class.toString())){
            return a.amountOfHour
        }
        else
        {
            List<Whoa> res = new ArrayList<Whoa>()
            if(isConnectionStarted()){
                ResultSet rs5
                stmt = conn.createStatement();
                // TODO filter with the current container
                query = "SELECT id,hours,member_id,year FROM whoa WHERE works_in_project_id = "+ a.id;
                rs5 = stmt.executeQuery(query);
                while (rs5.next()){
    //                println("try get whoa:"+rs5.getString(1))
                   Whoa w = DBConnection.getWhoaEmpl(rs5)
                   w.worksInProject = a;
                   res.add(w)
                }
            }
            return res
        }
    }

    /** get the whoa specified by its Id and create it as an object **/
    public static Whoa getWhoa(String whoa){
        if(isConnectionStarted()){
            ResultSet rs3
            stmt = conn.createStatement();
            query = "select id,hours,member_id,year from whoa where id="+whoa;
            rs3 = stmt.executeQuery(query);
            rs3.next()
            Employee e = DBConnection.getEmployee(rs3.getString(3))
            //Project p = DBConnection.getProject(rs.getString(6))
            Whoa w = new Whoa(member:e,year:(1900+Integer.parseInt(rs3.getString(4))),hours:Double.parseDouble(rs3.getString(2)))
            w.id=Integer.parseInt(rs3.getString(1))
            return w
        }
    }


    /** get the whoa specified by its Id and create it as an object **/
    public static Whoa getWhoaEmpl(ResultSet r){
            Employee e = DBConnection.getEmployee(r.getString(3))
            //Project p = DBConnection.getProject(rs.getString(6))
            Whoa w = new Whoa(member:e,year:(Integer.parseInt(r.getString(4))),hours:Double.parseDouble(r.getString(2)))
            w.id=Integer.parseInt(r.getString(1))
            return w
    }

    /** get the whoa specified by its Id and create it as an object **/
    public static Whoa getWhoa(ResultSet r){
            //Project p = DBConnection.getProject(rs.getString(6))
            //select id,hours,works_in_course_id,works_in_project_id,year from whoa where member_id="+emp.id;
            Whoa w = new Whoa(year:(Integer.parseInt(r.getString(4))),hours:Double.parseDouble(r.getString(2)))
            w.worksInProject = DBConnection.getProject(r.getString(3))
            w.id=Integer.parseInt(r.getString(1))
            return w
    }

    /**
     * update the amount of hour for a given project
     **/
    public static Integer updateAmountOfHour(Project p){
        try{
            //test if we need to delete some whoa
            //println("project to update:"+p)
            List<Whoa> temp = DBConnection.getWhoaList(p)
            //println("list:"+temp)
            for( Whoa w : temp  ){
                //println("boucle")
                boolean found=false
                for( Whoa w1 : p.amountOfHour){
                    if(w1.id==w.id){
                        found=true
                        break
                    }
                }
                if(!found){
                    DBConnection.deleteWhoa()
                }
            }
            //add all the current whoa
            if(p.amountOfHour && p.amountOfHour.size()>0){
                for(Whoa w : p.amountOfHour){
                    if(DBConnection.getWhoa(w.id)){ //if it exists already
                        //update it
                        DBConnection.updateWhoa(w)
                    }
                    else{ //if it doesn't exists
                        //create it
                        if(DBConnection.saveWhoa(w,p)<0){
                            println("err updateAmountOfHour- DBConnection")
                            return -1
                        }
                    }
                }
            }
        }
        catch(Exception e){
            println("exc updateAmountOfHour:"+e)
            return -1
        }
        
    }

    /** creates the given whoa in the database **/
    public static Integer saveWhoa(Whoa w, Project p){
        try{
            stmt = conn.createStatement();
            query = "insert into whoa values(0,0,"+w.hours+","+w.member.id+",null,"+p.id+","+w.year+")";
            //query = "insert into whoa values(0,0,"+w.hours+","+w.member.id+",null,"+p.id+","+w.year+",null,null)";
            stmt.execute(query);

            stmt = conn.createStatement();
            query = "select max(id) from whoa";
            rs = stmt.executeQuery(query);
            rs.next()
//            println("whoa inserted")
            return Integer.parseInt(rs.getString(1))
        }
        catch(Exception e){
            println("exc saveWhoa:"+e)
            return -1
        }
    }

    /** updates the given whoa in the database **/
    public static Integer updateWhoa(Whoa w){
        try{
            if(isConnectionStarted()){
                stmt = conn.createStatement();
                query = "update whoa set hours="+w.hours+", "
                query += "member_id="+w.member.id+", "
                if(w.worksInCourse){
                    query += "works_in_course_id="+w.worksInCourse.id+", "
                }
                else if(w.worksInProject){
                    query += "works_in_project_id="+w.worksInProject.id+", "
                }
                query += "year = "+w.year+" "
                query += "WHERE id = "+w.id+"";
                stmt.executeUpdate(query);
//                println("Whoa updated!")
                return 1
            }
            else{
                return -1
            }
        }
        catch(Exception e){
            println("exc updateWhoa:"+e)
            return -1
        }
    }

    /** delete the given whoa from the database **/
    public static Integer deleteWhoa(Whoa w){
        try{
            stmt = conn.createStatement();
            query = "delete from whoa where id="+w.id;

            stmt.execute(query);
//            println("whoa deleted")
            return 1
        }
        catch(Exception e){
            println("exc delWhoa:"+e)
            return -1
        }
    }

    /**************************************************************************/
    /*******          Access to the database concerning Budget          *******/
    /**************************************************************************/


    /** get the budget specified by its Id and create it as an object **/
    public static Budget getBudget(String bId){
        if(isConnectionStarted()){
            ResultSet rs7
            stmt = conn.createStatement();
            query = "select id,expected_total_income,known_total_income,known_variable_cost,year,last_year_balance,last_year_balance_set from budget where id="+bId;
//            println("query:"+query)
            rs7 = stmt.executeQuery(query);
            rs7.next()
//            println("oui ici")
            Budget b = new Budget(expectedTotalIncome:Float.parseFloat(rs7.getString(2)),year:Integer.parseInt(rs7.getString(5)),lastYearBalance:rs7.getString(6),lastYearBalanceSet:rs7.getString(7),knownTotalIncome:Float.parseFloat(rs7.getString(3)),knownVariableCost:Float.parseFloat(rs7.getString(4)))
//            println("22222222222")
            b.id=Integer.parseInt(rs7.getString(1))
            b.variableCosts = DBConnection.getVariableCostList(b)
//            println("333333333333")
            return b
        }
    }

    /** get the budget specified by its Id and create it as an object **/
    public static Budget getBudget(ResultSet r){
            Budget b = new Budget(expectedTotalIncome:Float.parseFloat(r.getString(2)),year:Integer.parseInt(r.getString(5)),lastYearBalance:r.getString(6),lastYearBalanceSet:r.getString(7),knownTotalIncome:Float.parseFloat(r.getString(3)),knownVariableCost:Float.parseFloat(r.getString(4)))
//            println("22222222222")
            b.id=Integer.parseInt(r.getString(1))
            b.variableCosts = DBConnection.getVariableCostList(b)
//            println("333333333333")
            return b

    }

    /**
    * This function returns the list of Budgets for the project with the given Id
    **/
    public static List<Budget> getBudgetList(Project p){
            List<Budget> res = new ArrayList<Budget>()
            if(isConnectionStarted()){
                ResultSet rs6
                stmt = conn.createStatement();
                query = "select id,expected_total_income,known_total_income,known_variable_cost,year,last_year_balance,last_year_balance_set from budget where project_id="+p.id;
                rs6 = stmt.executeQuery(query);
                while (rs6.next()){
    //                println("try budget: "+rs6.getString(1))
                   Budget b = DBConnection.getBudget(rs6)
                   b.project=p
    //               println("ok pour budget:"+b)
                   res.add(b)
                }
            }
            p.budgets=res
            return res
    }

    /** creates the given budget in the database **/
    public static Integer saveBudget(Project p, Budget b){
        def budgetId
        try{
            // Insert into budget
            stmt = conn.createStatement();
            query = "insert into budget values(0,0,"+b.expectedTotalIncome+","+b.knownTotalIncome+","+b.knownVariableCost+","+b.lastYearBalance+","+b.lastYearBalanceSet+","+p.id+","+b.year+")";
            //query = "insert into budget values(0,0,"+b.expectedTotalIncome+","+b.knownTotalIncome+","+b.knownVariableCost+","+b.year+",null,"+b.lastYearBalance+","+b.lastYearBalanceSet+","+p.id+")";
            stmt.execute(query);

            stmt = conn.createStatement();
            query = "select max(id) from budget";
            rs = stmt.executeQuery(query);
            rs.next()
//            println("budget inserted")
            budgetId = Integer.parseInt(rs.getString(1))

        }
        catch(Exception e){
            println("exc saveBudget1:"+e)
            return -1
        }
    }


    /** updates the given budget in the database **/
    public static Integer updateBudget(Budget b){
        try{
            if(isConnectionStarted()){
                stmt = conn.createStatement();
                query = "update budget set expected_total_income="+b.expectedTotalIncome+", "
                query += "known_total_income="+b.knownTotalIncome+", "
                query += "last_year_balance="+b.lastYearBalance+", "
                query += "last_year_balance_set="+b.lastYearBalanceSet+", "
                query += "known_variable_cost="+b.knownVariableCost
                query += " where id="+b.id;
                stmt.executeUpdate(query);
//                println("Budget updated:"+b.id)
                return 1
            }
            else{
                return -1
            }
        }
        catch(Exception e){
            println("exc updateBudget:"+e)
            return -1
        }
    }



    /**************************************************************************/
    /*******       Access to the database concerning VariableCost       *******/
    /**************************************************************************/


    /** get the budget specified by its Id and create it as an object **/
    public static VariableCost getVariableCost(String vId){
        if(isConnectionStarted()){
            ResultSet rs8
            stmt = conn.createStatement();
            query = "select * from variable_cost where id="+vId;
            rs8 = stmt.executeQuery(query);
            rs8.next()
            VariableCost v = new VariableCost(amount:Float.parseFloat(rs8.getString(3)),category:CostCategory.get(rs8.getString(4)))
            v.id=Integer.parseInt(rs8.getString(1))
            return v
        }
    }

    /** get the budget specified by its Id and create it as an object **/
    public static VariableCost getVariableCost(ResultSet r){
            VariableCost v = new VariableCost(amount:Float.parseFloat(r.getString(2)),category:CostCategory.get(r.getString(3)))
            v.id=Integer.parseInt(r.getString(1))
            return v

    }

    /**
    * This function returns the list of Budgets for the project with the given Id
    **/
    public static List<VariableCost> getVariableCostList(Budget b){
        List<VariableCost> res = new ArrayList<VariableCost>()
        if(isConnectionStarted()){
            ResultSet rs9
            stmt = conn.createStatement();
            query = "select id,amount,category_id from budget_variable_cost inner join variable_cost on variable_cost_id=id where budget_variable_costs_id="+b.id+" order by variable_cost_id";
            rs9 = stmt.executeQuery(query);
            while (rs9.next()){
               VariableCost v = DBConnection.getVariableCost(rs9)
               res.add(v)
            }
        }
        return res
    }

    /** updates the given budget in the database **/
    public static Integer updateVariableCost(VariableCost v){
        try{
            if(isConnectionStarted()){
                stmt = conn.createStatement();
                query = "update variable_cost set amount="+v.amount+", "
                query += "category_id="+v.category.id
                query += " where id="+v.id;
                stmt.executeUpdate(query);
//                println("VariableCost updated:"+v.id)
                return 1
            }
            else{
                return -1
            }
        }
        catch(Exception e){
            println("exc updateBudget:"+e)
            return -1
        }
    }

    /** creates the given whoa in the database **/
    public static Integer saveVariableCost(Budget b,Float amount, CostCategory c){
        def vId
        try{
            stmt = conn.createStatement();
            query = "insert into variable_cost values(0,0,"+amount+","+c.id+")";
            //query = "insert into variable_cost values(0,0,"+amount+","+c.id+",null,null)";
            stmt.execute(query);
//            println("variable cost inserted")

            stmt = conn.createStatement();
            query = "select max(id) from variable_cost";
            rs = stmt.executeQuery(query);
            rs.next()
            vId=rs.getString(1)
        }
        catch(Exception e){
            println("exc saveVariableCost1:"+e)
            return -1
        }


        try{
            stmt = conn.createStatement();
            //query = "insert into whoa values(0,0,"+w.hours+","+w.member.id+",null,"+p.id+","+w.year+")";
            query = "insert into budget_variable_cost values("+b.id+","+vId+")";
            stmt.execute(query);
//            println("budget_v_c inserted")
            return 1
        }
        catch(Exception e){
            println("exc saveVariableCost2:"+e)
            return -1
        }
    }

}

